﻿namespace com.vs.DataAccess
{
    using com.vs.General;
    using System;
    using System.Data;
    using System.Text;

    public class SqlRetrieval
    {
        private const int _inClauseLimit = 250;
        private static bool _isMultiByteDBLoaded = false;
        private static bool _multiByte = false;
        private const string _ORACLE = "#ORACLE";
        private const string _ORACLEDATETIMEFORMAT = "MM/DD/YYYY HH:MI:SS AM";
        private const string _USERPARAM = "@USERPARAM";

        public static string ConverFieldToChar(string fieldName, Session session)
        {
            return new StringBuilder(" RTRIM(CAST(").Append(fieldName).Append(" as " + GetMultiByteVarchar() + "))").ToString();
        }

        public static string ConvertNumericFieldToChar(string fieldName, Session session)
        {
            return ConverFieldToChar(fieldName, session);
        }

        public static string ConvertStringFieldToNumber(string fieldName, Session session)
        {
            return (" CONVERT(NUMERIC, RTRIM(LTRIM(" + fieldName + ")))");
        }

        public static string GetConcatenationOperator(Session session)
        {
            return "+";
        }

        public static string GetDBEmptyFieldSQL(Session session)
        {
            return " CAST('' AS NVARCHAR(2000)) ";
        }

        public static string GetGenericFromClause(Session session)
        {
            return "";
        }

        public static string GetLengthFunctionSql(Session session, string param)
        {
            return (" NVL(LENGTH(" + param + "),0) ");
        }

        public static string GetMBSafeChar()
        {
            return (IsMultiByteDB ? "nchar" : "char");
        }

        private static string GetMultiByteVarchar()
        {
            if (IsMultiByteDB)
            {
                return "nvarchar";
            }
            return "varchar";
        }

        public static string GetResultCountSQL(Session session, string sql)
        {
            return ("select count(" + MaskStringLiteral("x") + ") from ( " + sql + " )");
        }

        public static string GetSqlForCurrentDateNoTime(Session Session)
        {
            return ("convert(datetime,convert(" + GetMBSafeChar() + "(10),getdate(),101))");
        }

        public static string GetSqlForDate(string val, string format, Session session)
        {
            return ("to_date(" + MaskStringLiteral(val) + ", " + MaskStringLiteral(format) + ")");
        }

        public static string GetSqlForNoLock(Session session)
        {
            return " WITH (NOLOCK) ";
        }

        public static string GetSqlForSubStringFunction(Session session, string expression, int startIndex, int length)
        {
            return string.Concat(new object[] { " SUBSTRING(", expression, ",", startIndex, ",", (length == 0) ? ("len(" + expression + ")-" + Convert.ToString((int) (startIndex - 1))) : Convert.ToString(length), ")" });
        }

        public static string GetTableFunctionSQL(string funcName, string tableType, string[] parmArray, Session session)
        {
            StringBuilder builder = new StringBuilder(250);
            builder.Append(" dbo.").Append(funcName).Append("(");
            if (parmArray != null)
            {
                for (int i = 0; i < parmArray.Length; i++)
                {
                    if ((parmArray[i] != null) && parmArray[i].Equals("''"))
                    {
                        parmArray[i] = null;
                    }
                    builder.Append(parmArray[i]).Append(",");
                }
                builder.Length--;
            }
            builder.Append(")");
            return builder.ToString();
        }

        public static string GetTableNameForColumnList(Session session)
        {
            return "information_schema.columns";
        }

        public static string GetTableNameForTableList(Session session)
        {
            return "information_schema.tables";
        }

        public static string IsNumber(string param, Session session)
        {
            return ("NVL(LENGTH(TRIM(TRANSLATE(" + param + ",'0123456789', ' ') )),0) = 0 ");
        }

        public static string MaskStringLiteral(string param)
        {
            return MaskStringLiteral(param, false);
        }

        public static string MaskStringLiteral(string param, bool IsDataTableSelect)
        {
            if (StringManager.IsEmpty(param))
            {
                return "null";
            }
            StringBuilder builder = new StringBuilder(50);
            if (!(IsDataTableSelect || !_multiByte))
            {
                builder.Append("N");
            }
            param = param.Replace("'", "''");
            builder.Append("'").Append(param).Append("'");
            return builder.ToString();
        }

        public static IDataParameter[] RetrieveAccessParams(Session sess)
        {
            using (DataHelper helper = new DataHelper(sess))
            {
                return new IDataParameter[] { helper.BuildParameter("@USERPARAM", sess.User) };
            }
        }

        public static string RetrieveStoredProcCall(string procName, Session session, params string[] parmArray)
        {
            StringBuilder builder = new StringBuilder(250);
            builder.Append("exec ").Append(procName).Append(" ");
            if (parmArray != null)
            {
                for (int i = 0; i < parmArray.Length; i++)
                {
                    if (i != 0)
                    {
                        builder.Append(",");
                    }
                    if ((parmArray[i] != null) && StringManager.Equals(parmArray[i], "''"))
                    {
                        parmArray[i] = null;
                    }
                    builder.Append(parmArray[i]);
                }
            }
            return builder.ToString();
        }

        public static int InClauseLimit
        {
            get
            {
                return 250;
            }
        }

        public static bool IsMultiByteDB
        {
            get
            {
                if (!_isMultiByteDBLoaded)
                {
                    _isMultiByteDBLoaded = true;
                    _multiByte = ConfigManager.GetConfig("DB/MultiByte").ToString().ToUpper().Equals("Y");
                }
                return _multiByte;
            }
        }
    }
}

